notebook.community

Edit and run

CSV Data to MySQL for use in VISDOM

This notebook can be used to construct an 'account' table and a 'meter_data' table in mysql, based on the csv files with data extracted from the prop39schools xml files, and an 'intervention' table based on the PEPS_Data.xlsx file available on the prop39 data site.

The processed csv files can be downloaded as a zip file from here. They should be unzipped locally before running this script. Note that you may also need to pip install xlrd to be able to run pandas.read_excel for the intervention table data.

You must first create a database in mysql with your desired name (e.g. visdom_data_PGE), and create a data_db.cfg file to point to it, as described in the next section below.

The script will likely take about 40 minutes to complete on most modern laptops.

For your database to be ready for use in VISDOM, you will also need to load a local_weather table into your database. To do so you can follow the instructions in the local-weather repo and use the prop39_config.csv file found here, which was prepared using the accompanying notebook. Or, if you want to get to the end point faster, you can download this csv file, which was constructed using that repo, and then simply modify this sql query to point to that csv file and run it. Running the sql script will take about 20 minutes.

Once the database is set up, you can set it up as a DATA_SOURCE in VISDOM with the accompanying prop39_visdom_data_source.R file and test it via the sanitycheck function as follows:

source("prop39_visdom_data_source.R")
DATA_SOURCE = MyDataSource()
sanityCheckDataSource(DATA_SOURCE)

As described in more detail in the ID_mapping notebook in the same folder as this notebook, an account_uuid generally corresponds with an individual school and may have one or multiple meters associated with it, and may also have multiple interventions (or no interventions) associated with it.

File locations, database config

Point this to the directory with the meter data csv files:





In [1]:



    


csv_dir = "PGE_csv"

Read your database connection details from a data_db.cfg file with the following format:

dbType=MySQL
user=[database user]
pass=[password (if applicable)]
db=[name of database]




In [2]:



    


db_pass = ""
with open('data_db.cfg','r') as f:
    for line in f:
        s = line.split("=")
        if s[0].strip() == "user":
            db_user = s[1].strip()
        if s[0].strip() == "pass":
            db_pass = ":" + s[1].strip()
        if s[0].strip() == "db":
            db_db = s[1].strip()

Notebook config





In [3]:



    


import pandas as pd
import numpy as np
import mysql.connector, os, datetime
from sqlalchemy import create_engine



    











In [4]:



    


engine = create_engine('mysql+mysqlconnector://' + db_user + db_pass + '@localhost/' + db_db, echo=False)
conn = engine.connect()

Reading the account table data from the _BILL.csv files





In [5]:



    


usecols = [
    'utility',
    'customer_name',
    'customer_city',
    'customer_zip',
    'customer_account',
    'lea_customer',
    'cds_code',
    'school_site_name',
    'school_city',
    'school_site_zip',
    'agreement',
    'rate_schedule_id'    
]



    











In [6]:



    


accounts_list = []
for root, dirs, files in os.walk(csv_dir):
    for f in files:
        if f.endswith('_BILL.csv'):
            df = pd.read_csv(os.path.join(root,f), usecols=usecols)
            df = df.drop_duplicates()
            accounts_list.extend(df.to_dict(orient='records'))

accounts_df = df.from_records(accounts_list)
accounts_df = accounts_df.drop_duplicates()
print len(accounts_df)
accounts_df.head(3)



    


















    






4872










    
Out[6]:










  
    

      
      agreement
      cds_code
      customer_account
      customer_city
      customer_name
      customer_zip
      lea_customer
      rate_schedule_id
      school_city
      school_site_name
      school_site_zip
      utility
    

  
  
    

      0
      8496493494
      1100170130419
      NaN
      HAYWARD
      ALAMEDA COUNTY OFFICE OF EDUCATION
      94544-1136
      NaN
      E19S
      Hayward
      Alameda County Community
      94544-1136
      PacificGasElectric
    

    

      1
      8496493494
      1100176106751
      NaN
      HAYWARD
      ALAMEDA COUNTY OFFICE OF EDUCATION
      94544-1136
      NaN
      E19S
      Hayward
      Alameda County Special Education
      94544-1136
      PacificGasElectric
    

    

      2
      5637199749
      1612340000000
      NaN
      NEWARK
      NEWARK UNIFIED SCHOOL DISTRICT
      94560-2554
      NaN
      HA10SX
      Newark
      LEA
      94560-0385
      PacificGasElectric
    

  




















In [7]:



    


accounts_df.columns.tolist()



    


















    
Out[7]:








['agreement',
 'cds_code',
 'customer_account',
 'customer_city',
 'customer_name',
 'customer_zip',
 'lea_customer',
 'rate_schedule_id',
 'school_city',
 'school_site_name',
 'school_site_zip',
 'utility']

















In [8]:



    


accounts_df.columns = [
 'meter_uuid',
 'account_uuid',
 'customer_account',
 'customer_city',
 'customer_name',
 'customer_zip',
 'lea_customer',
 'rate_schedule_id',
 'school_city',
 'school_site_name',
 'school_site_zip',
 'utility_name'
]



    











In [9]:



    


accounts_df['zip5'] = accounts_df['school_site_zip'].str[:5]
accounts_df.head(3)



    


















    
Out[9]:










  
    

      
      meter_uuid
      account_uuid
      customer_account
      customer_city
      customer_name
      customer_zip
      lea_customer
      rate_schedule_id
      school_city
      school_site_name
      school_site_zip
      utility_name
      zip5
    

  
  
    

      0
      8496493494
      1100170130419
      NaN
      HAYWARD
      ALAMEDA COUNTY OFFICE OF EDUCATION
      94544-1136
      NaN
      E19S
      Hayward
      Alameda County Community
      94544-1136
      PacificGasElectric
      94544
    

    

      1
      8496493494
      1100176106751
      NaN
      HAYWARD
      ALAMEDA COUNTY OFFICE OF EDUCATION
      94544-1136
      NaN
      E19S
      Hayward
      Alameda County Special Education
      94544-1136
      PacificGasElectric
      94544
    

    

      2
      5637199749
      1612340000000
      NaN
      NEWARK
      NEWARK UNIFIED SCHOOL DISTRICT
      94560-2554
      NaN
      HA10SX
      Newark
      LEA
      94560-0385
      PacificGasElectric
      94560
    

  




















In [10]:



    


reals = accounts_df[['account_uuid']].applymap(np.isreal)
accounts_df = accounts_df[reals['account_uuid']]
len(accounts_df)



    


















    
Out[10]:








4864

















In [11]:



    


accounts_df = accounts_df.drop_duplicates(subset=['meter_uuid'], keep='last')
len(accounts_df)



    


















    
Out[11]:








3177

















In [12]:



    


accounts_df = accounts_df.dropna(subset=['zip5'])
len(accounts_df)



    


















    
Out[12]:








2575

Creating the account table in the desired format and writing to it





In [13]:



    


create_table_sql = '''
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_uuid` varchar(20) DEFAULT NULL,
  `meter_uuid` varchar(20) DEFAULT NULL,
  `zip5` varchar(5) DEFAULT NULL,
  `customer_account` varchar(50) DEFAULT NULL,
  `customer_city` varchar(50) DEFAULT NULL,
  `customer_name` varchar(50) DEFAULT NULL,
  `customer_zip` varchar(10) DEFAULT NULL,
  `lea_customer` varchar(50) DEFAULT NULL,
  `rate_schedule_id` varchar(50) DEFAULT NULL,
  `school_city` varchar(50) DEFAULT NULL,
  `school_site_name` varchar(100) DEFAULT NULL,
  `school_site_zip` varchar(10) DEFAULT NULL,
  `utility_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `zip5_meter_uuid_idx` (`METER_UUID`,`zip5`),
  KEY `account_uuid_idx` (`ACCOUNT_UUID`),
  KEY `meter_uuid_idx` (`METER_UUID`)
)
'''

conn.execute('DROP TABLE IF EXISTS `account`;')
conn.execute(create_table_sql)



    


















    
Out[13]:








<sqlalchemy.engine.result.ResultProxy at 0x10a349550>

















In [14]:



    


accounts_df.to_sql(name='account', con=engine, if_exists='append', index=False)

Quick test to make sure it's working:





In [15]:



    


pd.read_sql('SELECT * FROM account LIMIT 3;', con=engine)



    


















    
Out[15]:










  
    

      
      id
      account_uuid
      meter_uuid
      zip5
      customer_account
      customer_city
      customer_name
      customer_zip
      lea_customer
      rate_schedule_id
      school_city
      school_site_name
      school_site_zip
      utility_name
    

  
  
    

      0
      1
      1100176106751
      8496493494
      94544
      None
      HAYWARD
      ALAMEDA COUNTY OFFICE OF EDUCATION
      94544-1136
      None
      E19S
      Hayward
      Alameda County Special Education
      94544-1136
      PacificGasElectric
    

    

      1
      2
      1612340000000
      5637199749
      94560
      None
      NEWARK
      NEWARK UNIFIED SCHOOL DISTRICT
      94560-2554
      None
      HA10S
      Newark
      LEA
      94560-0385
      PacificGasElectric
    

    

      2
      3
      1612340130054
      5637199200
      94560
      None
      NEWARK
      NEWARK UNIFIED SCHOOL DISTRICT
      94560-2554
      None
      A10S
      Newark
      Newark Memorial High
      94560-5007
      PacificGasElectric
    

  




















In [16]:



    


pd.read_sql('SELECT COUNT(*) FROM account;', con=engine)



    


















    
Out[16]:










  
    

      
      COUNT(*)
    

  
  
    

      0
      2575
    

  




















In [17]:



    


pd.read_sql('SELECT COUNT(DISTINCT(meter_uuid)) FROM account;', con=engine)



    


















    
Out[17]:










  
    

      
      COUNT(DISTINCT(meter_uuid))
    

  
  
    

      0
      2575

Creating the intervention table

The following will read the interventions data from the PEPS_data.xlsx file, add the appropriate account_uuid to each entry (if applicable, Null otherwise) by left-merging with the accounts_df table, then edits column names for a few columns to keep them under the requisite 64 characters, and then writes it to a mysql table.





In [ ]:



    


accounts_df['site_pair'] = accounts_df['school_city'] + "_" + accounts_df['school_site_name']

interventions_df = pd.read_excel('PEPS_Data.xlsx', sheetname='Data- Approved EEPs')

interventions_df['site_pair'] = interventions_df['Site City'] + "_" + interventions_df['Site Name']
interventions_df = pd.merge(interventions_df,accounts_df[['site_pair','account_uuid']],how='left',on='site_pair')
del interventions_df['site_pair']

replacement_column_names = [
    'Grant Amount Req Based on Single or Multiple Years Allocation',
    'Grant Amount Req',
    'Were Planning Funds Requested from CA Department of Education',
    'Budget for Screening and Energy Audits Over Program Life',
    'Budget for Prop 39 Program Assistance Over Program Life',
    'Est First Yr Annual Electricity Production of PV Measure',
    'Est Total Rebates Plus Oth Non-Repayable Funds for PV Measure',
    'Est First Year Elec Prod of PPA Measure Generation System',
    'Est PPA Measure Elec Gen as Percent of Baseline Elec Usage'
]

k = 0
for j,i in enumerate(interventions_df.columns):
    if len(i) > 64:
        interventions_df.columns.values[j] = replacement_column_names[k]
        k += 1

conn.execute('DROP TABLE IF EXISTS `intervention`;')
interventions_df.to_sql(name='intervention', con=engine, chunksize=100)
conn.execute('ALTER TABLE intervention MODIFY account_uuid VARCHAR(20);')

Creating the meter_data table in the desired format

Note: this is currently treating the meter_uuid, account_uuid, date and zip5 as integers, but they should more likely be treated as varchar, varchar, datetime and varchar, respectively.





In [18]:



    


create_table_sql = '''
CREATE TABLE `meter_data` (
  `meter_uuid` varchar(20) NOT NULL,
  `account_uuid` varchar(20) NOT NULL,
  `date` DATE NOT NULL,
  `zip5` varchar(5) DEFAULT NULL,
'''

for i in range(1,97):
    create_table_sql += "`h" + str(i) + "` int(11) DEFAULT NULL,\n"

create_table_sql += '''    
  PRIMARY KEY (`meter_uuid`,`date`),
  KEY `meter_uuid_idx` (`meter_uuid`),
  KEY `account_uuid_idx` (`account_uuid`),
  KEY `zip_Date_idx` (`date`,`zip5`),
  KEY `zip_idx` (`zip5`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
'''

conn.execute('DROP TABLE IF EXISTS `meter_data`;')
conn.execute(create_table_sql)



    


















    
Out[18]:








<sqlalchemy.engine.result.ResultProxy at 0x10a4521d0>

Fill the meter table with data from the csv files





In [19]:



    


usecols = ['agreement', 'start']
for i in range(1,97):
    usecols.append('d' + str(i))



    











In [20]:



    


colnames = ['meter_uuid', 'date']
for i in range(1,97):
    colnames.append('h' + str(i))



    











In [21]:



    


for root, dirs, files in os.walk(csv_dir):
    for f in files:
        if f.endswith('_INTERVAL.csv'):
            df = pd.read_csv(os.path.join(root,f), usecols=usecols)
            if len(df) > 0:
                df.columns = colnames
                df = df.drop_duplicates()
                for i in range(1,97):
                    df['h' + str(i)] = df['h' + str(i)] * 1000
                df = pd.merge(df,accounts_df[['meter_uuid','account_uuid','zip5']],on='meter_uuid')
                df['date'] = pd.to_datetime(df['date'], unit='s')
                try:
                    df.to_sql(name='meter_data', con=engine, if_exists='append', index=False)
                except:
                    print "failed sql insert. meter_uuid:" + str(df['meter_uuid'][0]) + ", filename: " + f.split("_Pacific")[0] + "..."



    


















    






failed sql insert. meter_uuid:8496493494, filename: 01100176106751_20122013...
failed sql insert. meter_uuid:5637199274, filename: 01612340135426_20122013...
failed sql insert. meter_uuid:5741361055, filename: 04615230123687_20122013...
failed sql insert. meter_uuid:1626781530, filename: 06616220630038_20122013...
failed sql insert. meter_uuid:6449693283, filename: 10739651030402_20122013...
failed sql insert. meter_uuid:64274618, filename: 12755151232107_20122013...
failed sql insert. meter_uuid:5158002781, filename: 15101571530302_20122013...
failed sql insert. meter_uuid:5158002781, filename: 15101571530310_20122013...
failed sql insert. meter_uuid:5158002772, filename: 15101576069496_20122013...
failed sql insert. meter_uuid:7689247028, filename: 20652012030047_20122013...
failed sql insert. meter_uuid:7689247028, filename: 20652012032357_20122013...
failed sql insert. meter_uuid:9227619884, filename: 29663572930048_20122013...
failed sql insert. meter_uuid:8417601597, filename: 29663572930089_20122013...
failed sql insert. meter_uuid:5294406586, filename: 29663572930113_20122013...
failed sql insert. meter_uuid:8417601597, filename: 29663572935500_20122013...
failed sql insert. meter_uuid:5866366635, filename: 31668866031199_20122013...
failed sql insert. meter_uuid:5866366071, filename: 31668866111694_20122013...
failed sql insert. meter_uuid:3897600242, filename: 39103973930195_20122013...
failed sql insert. meter_uuid:3897600242, filename: 39103973930468_20122013...
failed sql insert. meter_uuid:3897600242, filename: 39103976069215_20122013...
failed sql insert. meter_uuid:1470487436, filename: 39685020126011_20122013...
failed sql insert. meter_uuid:1470487436, filename: 39685023930054_20122013...
failed sql insert. meter_uuid:1470487436, filename: 39685023932308_20122013...
failed sql insert. meter_uuid:9762196893, filename: 39685856110944_20122013...
failed sql insert. meter_uuid:9762196864, filename: 39685856118921_20122013...
failed sql insert. meter_uuid:6160206005, filename: 51714566053334_20122013...
failed sql insert. meter_uuid:6274316019, filename: 52715065230057_20122013...
failed sql insert. meter_uuid:9507670702, filename: 52715065231675_20122013...
failed sql insert. meter_uuid:6274316019, filename: 52715065231709_20122013...
failed sql insert. meter_uuid:4699942360, filename: 52716546053672_20122013...
failed sql insert. meter_uuid:7605066005, filename: 53738335330030_20122013...
failed sql insert. meter_uuid:7605066005, filename: 53738335337423_20122013...
failed sql insert. meter_uuid:924264005, filename: 54722236054449_20122013...
failed sql insert. meter_uuid:6687384031, filename: 57727106056477_20122013...
failed sql insert. meter_uuid:8496493494, filename: 01100176106751_20132014...
failed sql insert. meter_uuid:5637199274, filename: 01612340135426_20132014...
failed sql insert. meter_uuid:4843924951, filename: 01612590112789_20132014...
failed sql insert. meter_uuid:184413092, filename: 01612590115576_20132014...
failed sql insert. meter_uuid:9143215870, filename: 01612590115592_20132014...
failed sql insert. meter_uuid:8446677162, filename: 01612590130575_20132014...
failed sql insert. meter_uuid:9936841295, filename: 01612590132688_20132014...
failed sql insert. meter_uuid:814914904, filename: 01612596001903_20132014...
failed sql insert. meter_uuid:4219809177, filename: 01612596002018_20132014...
failed sql insert. meter_uuid:9560371125, filename: 01612596002042_20132014...
failed sql insert. meter_uuid:9210112255, filename: 01612596002075_20132014...
failed sql insert. meter_uuid:4373591879, filename: 01612596002190_20132014...
failed sql insert. meter_uuid:1873565595, filename: 01612596002273_20132014...
failed sql insert. meter_uuid:5760377907, filename: 01612596057020_20132014...
failed sql insert. meter_uuid:9936841295, filename: 01612596096523_20132014...
failed sql insert. meter_uuid:9210112806, filename: 01612596118616_20132014...
failed sql insert. meter_uuid:4450405016, filename: 01613090114421_20132014...
failed sql insert. meter_uuid:3663088379, filename: 01613090137745_20132014...
failed sql insert. meter_uuid:4193207243, filename: 04614996003198_20132014...
failed sql insert. meter_uuid:5283008065, filename: 04615076003230_20132014...
failed sql insert. meter_uuid:7241342687, filename: 04615150437566_20132014...
failed sql insert. meter_uuid:5741361913, filename: 04615230123687_20132014...
failed sql insert. meter_uuid:1626781530, filename: 06616220630038_20132014...
failed sql insert. meter_uuid:1783024534, filename: 07616486003594_20132014...
failed sql insert. meter_uuid:1783024603, filename: 07616486057178_20132014...
failed sql insert. meter_uuid:1783024380, filename: 07616486113104_20132014...
failed sql insert. meter_uuid:4605906314, filename: 07617540730424_20132014...
failed sql insert. meter_uuid:4605906314, filename: 07617540731836_20132014...
failed sql insert. meter_uuid:4605906314, filename: 07617546004030_20132014...
failed sql insert. meter_uuid:4605906950, filename: 07617546004261_20132014...
failed sql insert. meter_uuid:4605906314, filename: 07617546004378_20132014...
failed sql insert. meter_uuid:2647606762, filename: 07617966119515_20132014...
failed sql insert. meter_uuid:7001780659, filename: 09618530932756_20132014...
failed sql insert. meter_uuid:7293441633, filename: 09619786109441_20132014...
failed sql insert. meter_uuid:4148911005, filename: 10619946005730_20132014...
failed sql insert. meter_uuid:785750409, filename: 10621170113555_20132014...
failed sql insert. meter_uuid:819478743, filename: 10621171030071_20132014...
failed sql insert. meter_uuid:5876764646, filename: 10621176109912_20132014...
failed sql insert. meter_uuid:3137190125, filename: 10621581032150_20132014...
failed sql insert. meter_uuid:1731247178, filename: 10621666103840_20132014...
failed sql insert. meter_uuid:7853120415, filename: 10622571033695_20132014...
failed sql insert. meter_uuid:710099874, filename: 10622571033729_20132014...
failed sql insert. meter_uuid:9027438752, filename: 10623726007066_20132014...
failed sql insert. meter_uuid:5574684250, filename: 10624141036086_20132014...
failed sql insert. meter_uuid:7855478564, filename: 10625396007413_20132014...
failed sql insert. meter_uuid:7855478564, filename: 10625396112387_20132014...
failed sql insert. meter_uuid:2452456593, filename: 10738091030121_20132014...
failed sql insert. meter_uuid:9494121565, filename: 10738096006001_20132014...
failed sql insert. meter_uuid:7035788768, filename: 10738096006019_20132014...
failed sql insert. meter_uuid:6449693283, filename: 10739651030402_20132014...
failed sql insert. meter_uuid:3449703773, filename: 10739996006696_20132014...
failed sql insert. meter_uuid:9220502768, filename: 10752341030352_20132014...
failed sql insert. meter_uuid:9220502170, filename: 10752346007223_20132014...
failed sql insert. meter_uuid:9956345333, filename: 10752751036938_20132014...
failed sql insert. meter_uuid:822805690, filename: 10767786006613_20132014...
failed sql insert. meter_uuid:6269283869, filename: 11626531132109_20132014...
failed sql insert. meter_uuid:6269283869, filename: 11626536007546_20132014...
failed sql insert. meter_uuid:3814277968, filename: 11626611130038_20132014...
failed sql insert. meter_uuid:9921774924, filename: 11765621133701_20132014...
failed sql insert. meter_uuid:5799287231, filename: 12628286007884_20132014...
failed sql insert. meter_uuid:5799287231, filename: 12628286116289_20132014...
failed sql insert. meter_uuid:4882085402, filename: 15634206009419_20132014...
failed sql insert. meter_uuid:8810590779, filename: 15634610107987_20132014...
failed sql insert. meter_uuid:1397614200, filename: 15634876009534_20132014...
failed sql insert. meter_uuid:7298118698, filename: 15635291530252_20132014...
failed sql insert. meter_uuid:1033036017, filename: 15635291535087_20132014...
failed sql insert. meter_uuid:9793426297, filename: 15635786010003_20132014...
failed sql insert. meter_uuid:7425241139, filename: 15637196009963_20132014...
failed sql insert. meter_uuid:1936852700, filename: 15739081530229_20132014...
failed sql insert. meter_uuid:5699695786, filename: 16638836010300_20132014...
failed sql insert. meter_uuid:8012169270, filename: 16739326010573_20132014...
failed sql insert. meter_uuid:9543437862, filename: 17640146107791_20132014...
failed sql insert. meter_uuid:3366320082, filename: 17640486010656_20132014...
failed sql insert. meter_uuid:4324472005, filename: 20651936023899_20132014...
failed sql insert. meter_uuid:7689247688, filename: 20652012030047_20132014...
failed sql insert. meter_uuid:7689247688, filename: 20652012032357_20132014...
failed sql insert. meter_uuid:1050672188, filename: 21653346068308_20132014...
failed sql insert. meter_uuid:852726682, filename: 21653756024392_20132014...
failed sql insert. meter_uuid:8009829914, filename: 23656072334563_20132014...
failed sql insert. meter_uuid:991326554, filename: 23739166025308_20132014...
failed sql insert. meter_uuid:1961542917, filename: 24657636025589_20132014...
failed sql insert. meter_uuid:2715917005, filename: 27660846026157_20132014...
failed sql insert. meter_uuid:5420015004, filename: 27661676026629_20132014...
failed sql insert. meter_uuid:2022597018, filename: 27738252730125_20132014...
failed sql insert. meter_uuid:2022597018, filename: 27738252730141_20132014...
failed sql insert. meter_uuid:8585073135, filename: 27754736066955_20132014...
failed sql insert. meter_uuid:1802879791, filename: 29663160125013_20132014...
failed sql insert. meter_uuid:1802879791, filename: 29663166027080_20132014...
failed sql insert. meter_uuid:9227619884, filename: 29663572930048_20132014...
failed sql insert. meter_uuid:5294406586, filename: 29663572930113_20132014...
failed sql insert. meter_uuid:2234532249, filename: 31668526031157_20132014...
failed sql insert. meter_uuid:5866366635, filename: 31668866031199_20132014...
failed sql insert. meter_uuid:5866366071, filename: 31668866111694_20132014...
failed sql insert. meter_uuid:9168988179, filename: 31750856118392_20132014...
failed sql insert. meter_uuid:2980939608, filename: 35675536035133_20132014...
failed sql insert. meter_uuid:7338366005, filename: 35675616035141_20132014...
failed sql insert. meter_uuid:3897600242, filename: 39103973930195_20132014...
failed sql insert. meter_uuid:3897600242, filename: 39103973930468_20132014...
failed sql insert. meter_uuid:3897600242, filename: 39103976069215_20132014...
failed sql insert. meter_uuid:1470487436, filename: 39685020126011_20132014...
failed sql insert. meter_uuid:1470487436, filename: 39685023930054_20132014...
failed sql insert. meter_uuid:1470487436, filename: 39685023932308_20132014...
failed sql insert. meter_uuid:9762196893, filename: 39685856110944_20132014...
failed sql insert. meter_uuid:9762196864, filename: 39685856118921_20132014...
failed sql insert. meter_uuid:7160450384, filename: 39685930112565_20132014...
failed sql insert. meter_uuid:9170967911, filename: 39686356042444_20132014...
failed sql insert. meter_uuid:74665604, filename: 39686763937406_20132014...
failed sql insert. meter_uuid:6178831276, filename: 39686766042543_20132014...
failed sql insert. meter_uuid:9385851205, filename: 40687594030268_20132014...
failed sql insert. meter_uuid:9925557005, filename: 40687916043186_20132014...
failed sql insert. meter_uuid:1126746819, filename: 40754574035762_20132014...
failed sql insert. meter_uuid:1316010027, filename: 41688746043509_20132014...
failed sql insert. meter_uuid:3364357972, filename: 42691796045355_20132014...
failed sql insert. meter_uuid:3364357972, filename: 42691796118434_20132014...
failed sql insert. meter_uuid:5336697239, filename: 42693104234613_20132014...
failed sql insert. meter_uuid:3230952005, filename: 42693284236345_20132014...
failed sql insert. meter_uuid:4758521601, filename: 42693366046072_20132014...
failed sql insert. meter_uuid:7012160280, filename: 43694190108126_20132014...
failed sql insert. meter_uuid:2012155200, filename: 43694274330015_20132014...
failed sql insert. meter_uuid:2012155185, filename: 43694274330031_20132014...
failed sql insert. meter_uuid:2012155307, filename: 43694274330312_20132014...
failed sql insert. meter_uuid:2012155185, filename: 43694274330353_20132014...
failed sql insert. meter_uuid:2012155211, filename: 43694274334900_20132014...
failed sql insert. meter_uuid:2012155022, filename: 43694274335428_20132014...
failed sql insert. meter_uuid:8928866826, filename: 43694684332433_20132014...
failed sql insert. meter_uuid:9439265391, filename: 43694846106991_20132014...
failed sql insert. meter_uuid:8241368575, filename: 43695836095384_20132014...
failed sql insert. meter_uuid:8241368575, filename: 43695836100325_20132014...
failed sql insert. meter_uuid:8042840988, filename: 43696664330429_20132014...
failed sql insert. meter_uuid:8042840485, filename: 43696664333522_20132014...
failed sql insert. meter_uuid:8042840402, filename: 43696664333795_20132014...
failed sql insert. meter_uuid:8042840586, filename: 43696664335949_20132014...
failed sql insert. meter_uuid:8042840463, filename: 43696664337200_20132014...
failed sql insert. meter_uuid:8042840036, filename: 43696666060107_20132014...
failed sql insert. meter_uuid:8730915908, filename: 43733874331039_20132014...
failed sql insert. meter_uuid:1021789005, filename: 44697326049563_20132014...
failed sql insert. meter_uuid:3600913481, filename: 44697576049571_20132014...
failed sql insert. meter_uuid:4412192005, filename: 44697736049613_20132014...
failed sql insert. meter_uuid:9533035100, filename: 44697996119077_20132014...
failed sql insert. meter_uuid:2949705947, filename: 44698074430179_20132014...
failed sql insert. meter_uuid:2949705947, filename: 44698074436754_20132014...
failed sql insert. meter_uuid:2949705616, filename: 44698076049852_20132014...
failed sql insert. meter_uuid:8616361534, filename: 45698564530804_20132014...
failed sql insert. meter_uuid:2078240023, filename: 45700036050330_20132014...
failed sql insert. meter_uuid:428871894, filename: 45700116050348_20132014...
failed sql insert. meter_uuid:2964449005, filename: 45700456050397_20132014...
failed sql insert. meter_uuid:2928820015, filename: 45700866050439_20132014...
failed sql insert. meter_uuid:4095528413, filename: 45701364530044_20132014...
failed sql insert. meter_uuid:4741340411, filename: 48705654830022_20132014...
failed sql insert. meter_uuid:2050193390, filename: 48705654830154_20132014...
failed sql insert. meter_uuid:2050193390, filename: 48705654830162_20132014...
failed sql insert. meter_uuid:4741340504, filename: 48705856103154_20132014...
failed sql insert. meter_uuid:1338879785, filename: 51713735130117_20132014...
failed sql insert. meter_uuid:1338879785, filename: 51713735132758_20132014...
failed sql insert. meter_uuid:9507670702, filename: 52715065231675_20132014...
failed sql insert. meter_uuid:4512013005, filename: 52715146053508_20132014...
failed sql insert. meter_uuid:8126658005, filename: 52715306053524_20132014...
failed sql insert. meter_uuid:8337510005, filename: 52715486053532_20132014...
failed sql insert. meter_uuid:6926027235, filename: 52715636053557_20132014...
failed sql insert. meter_uuid:3520643644, filename: 54717956053839_20132014...
failed sql insert. meter_uuid:9299264205, filename: 54719696054126_20132014...
failed sql insert. meter_uuid:379036775, filename: 54721406054373_20132014...
failed sql insert. meter_uuid:924264622, filename: 54722236054449_20132014...
failed sql insert. meter_uuid:9699670064, filename: 57726945739552_20132014...
failed sql insert. meter_uuid:6687384031, filename: 57727106056477_20132014...
failed sql insert. meter_uuid:2807621697, filename: 58727286056600_20132014...
failed sql insert. meter_uuid:8496493494, filename: 01100176106751_20142015...
failed sql insert. meter_uuid:5637199274, filename: 01612340135426_20142015...
failed sql insert. meter_uuid:4843924951, filename: 01612590112789_20142015...
failed sql insert. meter_uuid:184413092, filename: 01612590115576_20142015...
failed sql insert. meter_uuid:9143215870, filename: 01612590115592_20142015...
failed sql insert. meter_uuid:8446677746, filename: 01612590130575_20142015...
failed sql insert. meter_uuid:9936841295, filename: 01612590132688_20142015...
failed sql insert. meter_uuid:814914904, filename: 01612596001903_20142015...
failed sql insert. meter_uuid:4219809177, filename: 01612596002018_20142015...
failed sql insert. meter_uuid:9560371125, filename: 01612596002042_20142015...
failed sql insert. meter_uuid:3574803057, filename: 01612596002075_20142015...
failed sql insert. meter_uuid:4373591879, filename: 01612596002190_20142015...
failed sql insert. meter_uuid:1873565595, filename: 01612596002273_20142015...
failed sql insert. meter_uuid:5760377907, filename: 01612596057020_20142015...
failed sql insert. meter_uuid:9936841295, filename: 01612596096523_20142015...
failed sql insert. meter_uuid:9210112806, filename: 01612596118616_20142015...
failed sql insert. meter_uuid:4450405016, filename: 01613090114421_20142015...
failed sql insert. meter_uuid:3663088379, filename: 01613090137745_20142015...
failed sql insert. meter_uuid:4193207243, filename: 04614996003198_20142015...
failed sql insert. meter_uuid:5283008065, filename: 04615076003230_20142015...
failed sql insert. meter_uuid:7241342687, filename: 04615150437566_20142015...
failed sql insert. meter_uuid:5741361913, filename: 04615230123687_20142015...
failed sql insert. meter_uuid:1626781530, filename: 06616220630038_20142015...
failed sql insert. meter_uuid:1783024534, filename: 07616486003594_20142015...
failed sql insert. meter_uuid:1783024603, filename: 07616486057178_20142015...
failed sql insert. meter_uuid:1783024380, filename: 07616486113104_20142015...
failed sql insert. meter_uuid:4605906314, filename: 07617540730424_20142015...
failed sql insert. meter_uuid:4605906314, filename: 07617540731836_20142015...
failed sql insert. meter_uuid:4605906397, filename: 07617540734764_20142015...
failed sql insert. meter_uuid:4605906959, filename: 07617546004030_20142015...
failed sql insert. meter_uuid:4605906950, filename: 07617546004261_20142015...
failed sql insert. meter_uuid:4605906314, filename: 07617546004378_20142015...
failed sql insert. meter_uuid:2647606762, filename: 07617966119515_20142015...
failed sql insert. meter_uuid:7001780659, filename: 09618530932756_20142015...
failed sql insert. meter_uuid:7293441633, filename: 09619786109441_20142015...
failed sql insert. meter_uuid:4148911737, filename: 10619946005730_20142015...
failed sql insert. meter_uuid:785750409, filename: 10621170113555_20142015...
failed sql insert. meter_uuid:819478743, filename: 10621171030071_20142015...
failed sql insert. meter_uuid:5876764646, filename: 10621176109912_20142015...
failed sql insert. meter_uuid:3137190125, filename: 10621581032150_20142015...
failed sql insert. meter_uuid:1731247178, filename: 10621666103840_20142015...
failed sql insert. meter_uuid:7853120415, filename: 10622571033695_20142015...
failed sql insert. meter_uuid:710099874, filename: 10622571033729_20142015...
failed sql insert. meter_uuid:9027438752, filename: 10623726007066_20142015...
failed sql insert. meter_uuid:5574684009, filename: 10624141036086_20142015...
failed sql insert. meter_uuid:7855478564, filename: 10625396007413_20142015...
failed sql insert. meter_uuid:7855478564, filename: 10625396112387_20142015...
failed sql insert. meter_uuid:2452456593, filename: 10738091030121_20142015...
failed sql insert. meter_uuid:9494121565, filename: 10738096006001_20142015...
failed sql insert. meter_uuid:7035788768, filename: 10738096006019_20142015...
failed sql insert. meter_uuid:6449693283, filename: 10739651030402_20142015...
failed sql insert. meter_uuid:3449703773, filename: 10739996006696_20142015...
failed sql insert. meter_uuid:9220502768, filename: 10752341030352_20142015...
failed sql insert. meter_uuid:9220502170, filename: 10752346007223_20142015...
failed sql insert. meter_uuid:9956345333, filename: 10752751036938_20142015...
failed sql insert. meter_uuid:822805690, filename: 10767786006613_20142015...
failed sql insert. meter_uuid:6269283869, filename: 11626531132109_20142015...
failed sql insert. meter_uuid:6269283869, filename: 11626536007546_20142015...
failed sql insert. meter_uuid:3814277968, filename: 11626611130038_20142015...
failed sql insert. meter_uuid:9921774924, filename: 11765621133701_20142015...
failed sql insert. meter_uuid:5799287231, filename: 12628286007884_20142015...
failed sql insert. meter_uuid:5799287231, filename: 12628286116289_20142015...
failed sql insert. meter_uuid:4882085402, filename: 15634206009419_20142015...
failed sql insert. meter_uuid:8810590779, filename: 15634610107987_20142015...
failed sql insert. meter_uuid:1397614200, filename: 15634876009534_20142015...
failed sql insert. meter_uuid:7298118698, filename: 15635291530252_20142015...
failed sql insert. meter_uuid:1033036017, filename: 15635291535087_20142015...
failed sql insert. meter_uuid:9793426297, filename: 15635786010003_20142015...
failed sql insert. meter_uuid:7425241139, filename: 15637196009963_20142015...
failed sql insert. meter_uuid:1936852700, filename: 15739081530229_20142015...
failed sql insert. meter_uuid:5699695786, filename: 16638836010300_20142015...
failed sql insert. meter_uuid:8012169270, filename: 16739326010573_20142015...
failed sql insert. meter_uuid:9543437862, filename: 17640146107791_20142015...
failed sql insert. meter_uuid:3366320082, filename: 17640486010656_20142015...
failed sql insert. meter_uuid:2237844448, filename: 20651936023899_20142015...
failed sql insert. meter_uuid:7689247688, filename: 20652012030047_20142015...
failed sql insert. meter_uuid:7689247688, filename: 20652012032357_20142015...
failed sql insert. meter_uuid:1050672188, filename: 21653346068308_20142015...
failed sql insert. meter_uuid:852726682, filename: 21653756024392_20142015...
failed sql insert. meter_uuid:8009829914, filename: 23656072334563_20142015...
failed sql insert. meter_uuid:991326554, filename: 23739166025308_20142015...
failed sql insert. meter_uuid:1961542917, filename: 24657636025589_20142015...
failed sql insert. meter_uuid:2715917567, filename: 27660846026157_20142015...
failed sql insert. meter_uuid:5420015088, filename: 27661676026629_20142015...
failed sql insert. meter_uuid:2022597018, filename: 27738252730125_20142015...
failed sql insert. meter_uuid:2022597018, filename: 27738252730141_20142015...
failed sql insert. meter_uuid:8585073135, filename: 27754736066955_20142015...
failed sql insert. meter_uuid:1802879791, filename: 29663160125013_20142015...
failed sql insert. meter_uuid:1802879791, filename: 29663166027080_20142015...
failed sql insert. meter_uuid:9227619884, filename: 29663572930048_20142015...
failed sql insert. meter_uuid:5294406586, filename: 29663572930113_20142015...
failed sql insert. meter_uuid:2234532249, filename: 31668526031157_20142015...
failed sql insert. meter_uuid:5866366090, filename: 31668866031199_20142015...
failed sql insert. meter_uuid:5866366071, filename: 31668866111694_20142015...
failed sql insert. meter_uuid:9168988179, filename: 31750856118392_20142015...
failed sql insert. meter_uuid:2980939608, filename: 35675536035133_20142015...
failed sql insert. meter_uuid:7338366128, filename: 35675616035141_20142015...
failed sql insert. meter_uuid:3897600242, filename: 39103973930195_20142015...
failed sql insert. meter_uuid:3897600242, filename: 39103973930468_20142015...
failed sql insert. meter_uuid:3897600242, filename: 39103976069215_20142015...
failed sql insert. meter_uuid:1470487436, filename: 39685020126011_20142015...
failed sql insert. meter_uuid:1470487436, filename: 39685023930054_20142015...
failed sql insert. meter_uuid:1470487436, filename: 39685023932308_20142015...
failed sql insert. meter_uuid:9762196893, filename: 39685856110944_20142015...
failed sql insert. meter_uuid:9762196864, filename: 39685856118921_20142015...
failed sql insert. meter_uuid:3137160274, filename: 39685930112565_20142015...
failed sql insert. meter_uuid:9170967911, filename: 39686356042444_20142015...
failed sql insert. meter_uuid:74665604, filename: 39686763937406_20142015...
failed sql insert. meter_uuid:6178831276, filename: 39686766042543_20142015...
failed sql insert. meter_uuid:7793407088, filename: 40687594030268_20142015...
failed sql insert. meter_uuid:7793407360, filename: 40687594030557_20142015...
failed sql insert. meter_uuid:7793407088, filename: 40687594033205_20142015...
failed sql insert. meter_uuid:7793407200, filename: 40687596043012_20142015...
failed sql insert. meter_uuid:7793407088, filename: 40687596043020_20142015...
failed sql insert. meter_uuid:7793407088, filename: 40687596043038_20142015...
failed sql insert. meter_uuid:7793407088, filename: 40687596043046_20142015...
failed sql insert. meter_uuid:7793407088, filename: 40687596043053_20142015...
failed sql insert. meter_uuid:7793407088, filename: 40687596043061_20142015...
failed sql insert. meter_uuid:7793407088, filename: 40687596043079_20142015...
failed sql insert. meter_uuid:7793407088, filename: 40687596043087_20142015...
failed sql insert. meter_uuid:7793407949, filename: 40687596043095_20142015...
failed sql insert. meter_uuid:7793407088, filename: 40687596043103_20142015...
failed sql insert. meter_uuid:7793407088, filename: 40687596043111_20142015...
failed sql insert. meter_uuid:7793407088, filename: 40687596043129_20142015...
failed sql insert. meter_uuid:7793407088, filename: 40687596109342_20142015...
failed sql insert. meter_uuid:9925557005, filename: 40687916043186_20142015...
failed sql insert. meter_uuid:1126746757, filename: 40754574035762_20142015...
failed sql insert. meter_uuid:1316010027, filename: 41688746043509_20142015...
failed sql insert. meter_uuid:3364357972, filename: 42691796045355_20142015...
failed sql insert. meter_uuid:3364357972, filename: 42691796118434_20142015...
failed sql insert. meter_uuid:5336697239, filename: 42693104234613_20142015...
failed sql insert. meter_uuid:3230952005, filename: 42693284236345_20142015...
failed sql insert. meter_uuid:4758521601, filename: 42693366046072_20142015...
failed sql insert. meter_uuid:7012160961, filename: 43694190108126_20142015...
failed sql insert. meter_uuid:2012155200, filename: 43694274330015_20142015...
failed sql insert. meter_uuid:2012155185, filename: 43694274330031_20142015...
failed sql insert. meter_uuid:2012155307, filename: 43694274330312_20142015...
failed sql insert. meter_uuid:2012155185, filename: 43694274330353_20142015...
failed sql insert. meter_uuid:2012155211, filename: 43694274334900_20142015...
failed sql insert. meter_uuid:2012155022, filename: 43694274335428_20142015...
failed sql insert. meter_uuid:8928866826, filename: 43694684332433_20142015...
failed sql insert. meter_uuid:9439265391, filename: 43694846106991_20142015...
failed sql insert. meter_uuid:8241368575, filename: 43695836095384_20142015...
failed sql insert. meter_uuid:8241368575, filename: 43695836100325_20142015...
failed sql insert. meter_uuid:8042840988, filename: 43696664330429_20142015...
failed sql insert. meter_uuid:8042840485, filename: 43696664333522_20142015...
failed sql insert. meter_uuid:8042840402, filename: 43696664333795_20142015...
failed sql insert. meter_uuid:8042840586, filename: 43696664335949_20142015...
failed sql insert. meter_uuid:8042840463, filename: 43696664337200_20142015...
failed sql insert. meter_uuid:8042840036, filename: 43696666060107_20142015...
failed sql insert. meter_uuid:8730915908, filename: 43733874331039_20142015...
failed sql insert. meter_uuid:1021789843, filename: 44697326049563_20142015...
failed sql insert. meter_uuid:3600913481, filename: 44697576049571_20142015...
failed sql insert. meter_uuid:4412192005, filename: 44697736049613_20142015...
failed sql insert. meter_uuid:9533035100, filename: 44697996119077_20142015...
failed sql insert. meter_uuid:2949705947, filename: 44698074430179_20142015...
failed sql insert. meter_uuid:2949705947, filename: 44698074436754_20142015...
failed sql insert. meter_uuid:2949705616, filename: 44698076049852_20142015...
failed sql insert. meter_uuid:8616361534, filename: 45698564530804_20142015...
failed sql insert. meter_uuid:2078240023, filename: 45700036050330_20142015...
failed sql insert. meter_uuid:428871894, filename: 45700116050348_20142015...
failed sql insert. meter_uuid:1050567005, filename: 45700376050389_20142015...
failed sql insert. meter_uuid:2964449420, filename: 45700456050397_20142015...
failed sql insert. meter_uuid:2928820015, filename: 45700866050439_20142015...
failed sql insert. meter_uuid:4095528413, filename: 45701364530044_20142015...
failed sql insert. meter_uuid:4741340411, filename: 48705654830022_20142015...
failed sql insert. meter_uuid:2050193390, filename: 48705654830154_20142015...
failed sql insert. meter_uuid:2050193390, filename: 48705654830162_20142015...
failed sql insert. meter_uuid:4741340504, filename: 48705856103154_20142015...
failed sql insert. meter_uuid:1338879785, filename: 51713735130117_20142015...
failed sql insert. meter_uuid:1338879785, filename: 51713735132758_20142015...
failed sql insert. meter_uuid:9507670514, filename: 52715065231675_20142015...
failed sql insert. meter_uuid:4512013005, filename: 52715146053508_20142015...
failed sql insert. meter_uuid:8126658005, filename: 52715306053524_20142015...
failed sql insert. meter_uuid:8337510005, filename: 52715486053532_20142015...
failed sql insert. meter_uuid:6926027235, filename: 52715636053557_20142015...
failed sql insert. meter_uuid:3520643644, filename: 54717956053839_20142015...
failed sql insert. meter_uuid:9299264205, filename: 54719696054126_20142015...
failed sql insert. meter_uuid:379036775, filename: 54721406054373_20142015...
failed sql insert. meter_uuid:924264579, filename: 54722236054449_20142015...
failed sql insert. meter_uuid:9699670064, filename: 57726945739552_20142015...
failed sql insert. meter_uuid:6687384031, filename: 57727106056477_20142015...
failed sql insert. meter_uuid:2807621697, filename: 58727286056600_20142015...

Quick tests to make sure it's done so properly





In [22]:



    


pd.read_sql('SELECT * FROM meter_data LIMIT 3;', con=engine)



    


















    
Out[22]:










  
    

      
      meter_uuid
      account_uuid
      date
      zip5
      h1
      h2
      h3
      h4
      h5
      h6
      ...
      h87
      h88
      h89
      h90
      h91
      h92
      h93
      h94
      h95
      h96
    

  
  
    

      0
      1021789005
      44697326049563
      2014-01-07
      95060
      2880
      0
      6160
      2720
      2240
      2480
      ...
      NaN
      NaN
      NaN
      NaN
      NaN
      NaN
      NaN
      NaN
      NaN
      NaN
    

    

      1
      1021789379
      44697326049563
      2014-01-08
      95060
      880
      880
      960
      880
      800
      880
      ...
      1600.0
      1680.0
      1600.0
      1760.0
      1600.0
      1600.0
      1360.0
      1120.0
      1120.0
      1280.0
    

    

      2
      1021789379
      44697326049563
      2014-01-09
      95060
      1040
      1440
      1120
      1200
      1120
      1120
      ...
      960.0
      720.0
      880.0
      880.0
      880.0
      880.0
      800.0
      960.0
      880.0
      880.0
    

  



3 rows × 100 columns



















In [23]:



    


pd.read_sql('SELECT COUNT(*) FROM meter_data;', con=engine)



    


















    
Out[23]:










  
    

      
      COUNT(*)
    

  
  
    

      0
      1286757

Coordinating between tables to make sure they match





In [24]:



    


conn.execute('DELETE FROM account WHERE meter_uuid NOT IN (SELECT DISTINCT(meter_uuid) FROM meter_data);')



    


















    
Out[24]:








<sqlalchemy.engine.result.ResultProxy at 0x10a1268d0>

















In [26]:



    


pd.read_sql('SELECT COUNT(*) FROM account;', con=engine)



    


















    
Out[26]:










  
    

      
      COUNT(*)
    

  
  
    

      0
      2186

Minor cludge that could be done better

This meter_uuid had only one meter_data day record associated with it for some reason, which would cause errors if allowed to stay in the database.





In [27]:



    


conn.execute("DELETE FROM account WHERE meter_uuid = '1021789005';")
conn.execute("DELETE FROM meter_data WHERE meter_uuid = '1021789005';")



    


















    
Out[27]:








<sqlalchemy.engine.result.ResultProxy at 0x10a126890>

Content source: sborgeson/prop39schools

Similar notebooks:

notebook.community | gallery | about